# -*- coding: utf-8 -*-


from datetime import date, datetime

from sqlalchemy import asc, func, event
from sqlalchemy.orm import scoped_session, sessionmaker, Query

from py_engine import engine, session
import logging

from py_table import User

logger = logging.getLogger(__name__)


def test_query():
    query: Query = session.query(User).filter(User.user_name == 'chench')
    # 单条结构和多条结构
    one_result = query.first()
    any_result = query.all()

    # SQL
    sql = str(query)  # 不带参数
    # 绑定参数
    script = str(query.statement.compile(compile_kwargs={"literal_binds": True}))
    pass


def test_query_column():
    query: Query = session.query(User.name == 'chench')
    any_result = query.distinct().all()
    columns = (User.update_column, User.user_id)
    data = query.values(*columns)
    pass


def test_offset_limit():
    # 分页：一定要排序
    query = session.query(User).order_by(asc(User.user_id))
    page = 2
    page_size = 10
    page_data = query.offset(page * page_size).limit(page_size).all()

    pass


def test_func():
    # 使用聚合函数加别名查询单条记录
    query: Query = session.query(
        func.max(User.user_id).label('max_id'),
        func.max(User.user_id).label('min_id'),
    )
    result = query.first()
    logger.info(f'max_id:{result.max_id},min_id:{result.min_id}.')
    pass


def test_filter():
    query: Query = session.query(User)

    # 查询过滤条件非空
    # query = query.filter(User.user_name is None)
    # query = query.filter(User.user_name is not None)
    # query = query.filter(User.user_name.is_(None))
    # query = query.filter(User.user_name.isnot(None))

    # data = query.filter(User.user_name.like('ch%')).all()
    #
    # data = query.filter(and_(User.user_name == 'chench', User.user_id >= 1)).all()
    # data = query.filter(or_(User.user_name == 'chench', User.user_id >= 1)).all()

    # in
    # user_ids = [12, 13]
    # count = query.filter(User.user_id.in_(user_ids)).count()

    # count = query.filter(User.user_id.between(11, 15)).count()

    # count = query.filter(User.user_name.startswith('name')).count()
    # count = query.filter(User.user_name.endswith('1')).count()
    # count = query.filter(User.user_name.contains('name')).count()
    # logger.info(f'count:{count}')

    start_date = datetime.strptime('2023-09-05', '%Y-%m-%d').date()
    end_date = datetime.strptime('2023-09-06', '%Y-%m-%d').date()

    query = query.filter(User.update_datetime.between(start_date, end_date))

    pass


def test_filter_by():
    query: Query = session.query(User)

    # 等于
    data = query.filter(User.user_id == 1).filter_by(user_id=1).all()

    pass


def test_order_by():
    query: Query = session.query(User)

    data = query.order_by(asc(User.user_name), asc(User.user_id)).all()

    pass


def test_group_by():
    query: Query = session.query(User)

    # 分组，排序
    group_result = session.query(
        User.user_name,
        func.count(User.user_name).label('row_count'),
    ).group_by(User.user_name).order_by(asc(User.user_name)).all()

    for item in group_result:
        logger.info(f'user_name:{item.user_name},row_count:{item.row_count}')
        pass

    pass


if __name__ == '__main__':
    test_query()
    # test_filter()
    # test_query_column()
    # test_filter_by()

    # test_group_by()

    pass
